USE [NANS21V]
GO

/****** オブジェクト: SqlProcedure [dbo].[AlltimeRecord] スクリプト日付: 2014/05/19 14:13:27 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AlltimeRecord]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[AlltimeRecord]
GO

CREATE PROCEDURE [dbo].[AlltimeRecord]
	@TAIKAINO int,
	@ORDERFLG int
	AS
	BEGIN
		DECLARE @SQL AS VARCHAR(MAX)

	SET @SQL = '
			SELECT  
				R.REKIDAINO, 
				R.COMMENTNO, 
				C.COMMENTMEI, 
				C.COMMENTRYAKU, 
				R.KYOGINO, 
				R.SABUNO , 
				'''' AS SABUMEI,
				K.KYOGIMEI, 
				K.SEIBETUNO, 
				dbo.Kiroku(R.KIROKU,SM.SYUMOKUKUBUN,SM.KIROKUKUBUN,R.SOKUTEI) AS KIROKU,
				R.SENSYUMEI, 
				R.SYOZOKUMEI, 
				R.KOJINSYOZOKUTIMEI, 
				R.HIDUKE, 
				dbo.Sokuteikubun(R.SOKUTEI) AS SOKUTEI,
				R.SOKUTEI,
				SM.KIROKUKUBUN, 
				SM.SYUMOKUKUBUN , 
				0 AS SUBKIROKUKUBUN ,
				0 AS SUBSYUMOKUKUBUN, 
				SM.PRINTNO AS SYUMOKU_PRINTNO, 
				SB.PRINTNO AS SYUBETU_PRINTNO, 
				C.PRINTNO AS COMMENT_PRINTNO, 
				0 AS ID 
			FROM 
				TBL_REKIDAI AS R, 
				TBL_KYOGI AS K, 
				TBL_COMMENT AS C, 
				TBL_SYUMOKU_MASTER AS SM, 
				TBL_SYUBETU AS SB 
			WHERE 
				R.TAIKAINO = K.TAIKAINO 
				AND R.KYOGINO = K.KYOGINO 
				AND R.COMMENTNO = C.COMMENTNO 
				AND K.SYUMOKUNO = SM.SYUMOKUNO 
				AND K.SYUBETUNO = SB.SYUBETUNO 
				AND R.SABUNO = 0 
				AND R.TAIKAINO = '+STR(@TAIKAINO) +'
				UNION 
					SELECT 
						R.REKIDAINO, 
						R.COMMENTNO, 
						C.COMMENTMEI, 
						C.COMMENTRYAKU,
						R.KYOGINO, 
						R.SABUNO , 
						SM.SYUMOKUMEI AS SABUMEI, 
						K.KYOGIMEI AS KYOGIMEI, 
						K.SEIBETUNO, 
						dbo.Kiroku(R.KIROKU,SM.SYUMOKUKUBUN,SM.KIROKUKUBUN,R.SOKUTEI) AS KIROKU,
						R.SENSYUMEI, 
						R.SYOZOKUMEI, 
						R.KOJINSYOZOKUTIMEI, 
						R.HIDUKE, 
						dbo.Sokuteikubun(R.SOKUTEI) AS SOKUTEI,
						R.SOKUTEI,
						SM2.KIROKUKUBUN,
						SM2.SYUMOKUKUBUN, 
						SM.KIROKUKUBUN AS SUBKIROKUKUBUN, 
						SM.SYUMOKUKUBUN AS SUBSYUMOKUKUBUN, 
						SM2.PRINTNO AS SYUMOKU_PRINTNO, 
						SB.PRINTNO AS SYUBETU_PRINTNO, 
						C.PRINTNO AS COMMENT_PRINTNO, 
						KM.ID AS ID 
					FROM 
						TBL_REKIDAI AS R, 
						TBL_KYOGI AS K, 
						TBL_COMMENT AS C, 
						TBL_KONSEI_MASTER AS KM, 
						TBL_SYUMOKU_MASTER AS SM, 
						TBL_SYUBETU AS SB, 
						TBL_SYUMOKU_MASTER AS SM2 
					WHERE 
						R.TAIKAINO = K.TAIKAINO 
						AND R.KYOGINO = K.KYOGINO 
						AND K.SYUMOKUNO = KM.SYUMOKUNO 
						AND K.SYUMOKUNO = SM2.SYUMOKUNO 
						AND K.SYUBETUNO = SB.SYUBETUNO 
						AND R.SABUNO = KM.KONSEINO 
						AND KM.KONSEINO = SM.SYUMOKUNO 
						AND R.COMMENTNO = C.COMMENTNO 
						AND R.TAIKAINO = '+STR(@TAIKAINO) +'
						UNION 
							SELECT 
								R.REKIDAINO, 
								R.COMMENTNO, 
								C.COMMENTMEI, 
								C.COMMENTRYAKU,
								R.KYOGINO, 
								R.SABUNO , 
								LM.LAPMEI AS SABUMEI, 
								K.KYOGIMEI, 
								K.SEIBETUNO, 
								dbo.Kiroku(R.KIROKU,SM.SYUMOKUKUBUN,SM.KIROKUKUBUN,R.SOKUTEI) AS KIROKU,
								R.SENSYUMEI, 
								R.SYOZOKUMEI, 
								R.KOJINSYOZOKUTIMEI, 
								R.HIDUKE, 
								dbo.Sokuteikubun(R.SOKUTEI) AS SOKUTEI,
								R.SOKUTEI,
								SM.KIROKUKUBUN,
								SM.SYUMOKUKUBUN, 
								0 AS SUBKIROKUKUBUN ,
								0 AS SUBSYUMOKUKUBUN, 
								SM.PRINTNO AS SYUMOKU_PRINTNO, 
								SB.PRINTNO AS SYUBETU_PRINTNO, 
								C.PRINTNO AS COMMENT_PRINTNO, 
								L.ID 
							FROM 
								TBL_REKIDAI AS R, 
								TBL_KYOGI AS K, 
								TBL_COMMENT AS C, 
								TBL_LAP AS L, 
								TBL_SYUMOKU_MASTER AS SM , 
								TBL_LAP_MASTER AS LM, 
								TBL_SYUBETU AS SB 
							WHERE 
								R.TAIKAINO = K.TAIKAINO 
								AND R.KYOGINO = K.KYOGINO 
								AND K.SYUMOKUNO = L.SYUMOKUNO 
								AND K.SYUBETUNO = SB.SYUBETUNO 
								AND R.SABUNO = L.LAPNO 
								AND L.LAPNO = LM.LAPNO 
								AND K.SYUMOKUNO = SM.SYUMOKUNO 
								AND R.COMMENTNO = C.COMMENTNO 
								AND R.TAIKAINO =' +STR(@TAIKAINO) +'
								'
								

						IF @ORDERFLG = 1
							SET @SQL = @SQL + 'ORDER BY K.SEIBETUNO, COMMENT_PRINTNO, SYUBETU_PRINTNO, SYUMOKU_PRINTNO, ID, R.REKIDAINO'					
						ELSE IF @ORDERFLG = 2
							SET @SQL = @SQL + 'ORDER BY K.SEIBETUNO, SYUBETU_PRINTNO, SYUMOKU_PRINTNO, ID, COMMENT_PRINTNO, R.REKIDAINO '
	PRINT(@SQL)
	EXECUTE(@SQL)

END